import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class DoSqlApplication {

    private static final String URL = "jdbc:mysql://192.168.1.170:23306/test";
    private static final String USER = "root";
    private static final String PASSWORD = "12345678";
    private static final String LOG_FILE = "data_update.log";

    public static void main(String[] args) {
        System.out.println("开始执行数据更新任务: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

        //许哟执行的操作
         transferData();
         updateSheet1FromSealMain();

        System.out.println("数据更新任务完成: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }

    public static void transferData() {
        // 建立数据库连接
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // 查询 source_table 中的数据
            String selectQuery = "SELECT * FROM seal_main";
            try (PreparedStatement selectStmt = connection.prepareStatement(selectQuery);
                 ResultSet resultSet = selectStmt.executeQuery()) {

                // 更新 target_table 中的数据
                String updateQuery = "UPDATE Sheet1 SET f5 = ?, f6 = ? WHERE id = ?";
                try (PreparedStatement updateStmt = connection.prepareStatement(updateQuery)) {
                    while (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("custcode");
                        int value = resultSet.getInt("principal");
                        // 设置更新语句的参数
                        updateStmt.setString(1, name);
                        updateStmt.setInt(2, value);
                        updateStmt.setInt(3, id);

                        // 执行更新操作
                        int rowsAffected = updateStmt.executeUpdate();
                        if (rowsAffected > 0) {
                            System.out.println("Updated row with id: " + id);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 更新A表数据，
     * 从b表中查询相关数据更新A表
     */
    public static void updateSheet1FromSealMain() {
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("数据库连接建立成功");
            logMessage("数据库连接建立成功");

            // 查询Sheet1中f5为空的记录
            String selectQuery = "SELECT * FROM Sheet1 WHERE f5 IS NULL";
            try (PreparedStatement selectStmt = connection.prepareStatement(selectQuery);
                 ResultSet resultSet = selectStmt.executeQuery()) {

                int processedCount = 0;
                int updatedCount = 0;
                int notFoundCount = 0;

                System.out.println("开始处理Sheet1中f5为空的记录...");
                logMessage("开始处理Sheet1中f5为空的记录...");

                while (resultSet.next()) {
                    processedCount++;
                    int id = resultSet.getInt("id");
                    String f4 = resultSet.getString("f4");

                    System.out.println("正在处理Sheet1记录 ID: " + id + ", f4值: " + f4);
                    logMessage("正在处理Sheet1记录 ID: " + id + ", f4值: " + f4);

                    // 从seal_main表查询相关信息
                    String infoQuery = "SELECT * FROM seal_main WHERE custname = ?";
                    try (PreparedStatement infoStmt = connection.prepareStatement(infoQuery)) {
                        infoStmt.setString(1, f4);
                        try (ResultSet infoResult = infoStmt.executeQuery()) {
                            if (infoResult.next()) {
                                // 找到对应信息，更新Sheet1
                                String updateQuery = "UPDATE Sheet1 SET f5 = ?, f6 = ?, f7 = ?, f10 = ?, f11 = ? WHERE id = ?";
                                try (PreparedStatement updateStmt = connection.prepareStatement(updateQuery)) {
                                    String custcode = infoResult.getString("custcode");
                                    int principal = infoResult.getInt("principal");
                                    String phoneno = infoResult.getString("phoneno");
                                    java.sql.Date maindealdate = infoResult.getDate("maindealdate");
                                    java.sql.Date mainchipdate = infoResult.getDate("mainchipdate");

                                    updateStmt.setString(1, custcode);
                                    updateStmt.setInt(2, principal);
                                    updateStmt.setString(3, phoneno);
                                    updateStmt.setDate(4, maindealdate);
                                    updateStmt.setDate(5, mainchipdate);
                                    updateStmt.setInt(6, id);

                                    int rowsAffected = updateStmt.executeUpdate();
                                    if (rowsAffected > 0) {
                                        updatedCount++;
                                        String message = "成功更新Sheet1记录 ID: " + id +
                                                " | custcode: " + custcode +
                                                " | principal: " + principal +
                                                " | phoneno: " + phoneno;
                                        System.out.println(message);
                                        logMessage(message);
                                    }
                                }
                            } else {
                                // 未找到对应信息，设置f4为1
                                notFoundCount++;
                                String updateQuery = "UPDATE Sheet1 SET f4 = 1 WHERE id = ?";
                                try (PreparedStatement updateStmt = connection.prepareStatement(updateQuery)) {
                                    updateStmt.setInt(1, id);
                                    updateStmt.executeUpdate();
                                }
                                String message = "未找到匹配的seal_main记录，已将Sheet1记录 ID: " + id + " 的f4设置为1";
                                System.out.println(message);
                                logMessage(message);
                            }
                        }
                    }
                }
                String summary = String.format("处理完成 - 总计处理: %d 条, 成功更新: %d 条, 未找到匹配: %d 条",
                        processedCount, updatedCount, notFoundCount);
                System.out.println(summary);
                logMessage(summary);
            }
        } catch (SQLException e) {
            String errorMessage = "数据库操作异常: " + e.getMessage();
            System.err.println(errorMessage);
            logMessage(errorMessage);
            e.printStackTrace();
        }
    }



    /**
     * 记录日志到文件
     * @param message 日志消息
     */
    private static void logMessage(String message) {
        try (PrintWriter writer = new PrintWriter(new FileWriter(LOG_FILE, true))) {
            String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
            writer.println("[" + timestamp + "] " + message);
        } catch (IOException e) {
            System.err.println("写入日志文件失败: " + e.getMessage());
        }
    }
}